clear all
set more off, perm
local mydir "`1'"
global Tables	`mydir'/XS/Tables
global Work	`mydir'/XS/DataWork
global Raw	`mydir'/XS/DataRaw
global WRDS	`mydir'/WRDS
/* ********************************************************************* */
* get market cap and adjustment factors from CRSP
import sas $WRDS/CRSP/dsf.sas7bdat, clear case(lower)

keep permco permno date shrout cfacshr prc 
keep if year(date)>1975 

replace  prc=abs(prc)
generate mcap=shrout*prc
drop prc
/* ******************** */
* deal with weekends
egen id=group(permco permno)
tsset id date
tsfill

foreach var of varlist permco permno shrout cfacshr mcap  {
	replace `var'=L1.`var' if `var'==.
}
drop id

gen statpers=date
format statpers %td

save $Work/mcap, replace
/* ******************** */
* Get IBES data
import sas $WRDS/IBES/statsumu_epsus.sas7bdat, clear case(lower)
rename *, lower
compress
keep if (index(measure,"EPS")>0  & index(curcode,"USD")>0) 
 
drop if index(fpi,"A") | index(fpi,"B") | index(fpi,"C") | index(fpi,"D")
destring fpi, replace
* *************
keep if fpi<=9

* fix some of the missing data
keep statpers ticker fpi medest fpedats

reshape wide medest fpedats, i(statpers ticker) j(fpi)
rename medest0 LTG
drop fpedats0

*use quarterly data to build one-year forecast
replace medest1 =medest6+medest7+medest8+medest9 	if  medest1==. & (medest6+medest7+medest8+medest9!=medest2) & (fpedats2>fpedats9)
replace fpedats1=fpedats9 				if fpedats1==. & (fpedats2>fpedats9)
drop *6 *7 *8 *9 

forvalues x=2(1)5 {
	local j=`x'-1
	replace fpedats`x'=fpedats`j'+365  		if fpedats`x'==.
}
forvalues x=3(1)5 {
	local j=`x'-1
	replace medest`x'=medest`j'*(1+LTG/100) if medest`x'==. & medest`j'>0
}
forvalues x=6(1)7 {
	generate fpedats`x'=fpedats5+365*(`x'-5)
	format fpedats`x' %td
	generate medest`x'=medest5*(1+LTG/100)^(`x'-5)	if medest5>0
}
* interpolate to obtain forecasts at t=+1, +2, ..., +5
preserve
keep ticker statpers LTG
tempfile LTG
save "`LTG'", replace
* *******************
restore
drop LTG
reshape long medest fpedats, i(ticker statpers) j(fpi)

egen id=group(ticker statpers)
gen fpdatem=mofd(fpedats)
format fpdatem %tm

* one obs has all missing values -- creates duplicate observations for fpedats
drop if ticker=="MICT" & fpedats==.

* one firm has two forecasts for 12/31/2002 made on 1/14/1999
drop if ticker=="FCX"  & fpedats==mdy(12,31,2002) & mdy(1,14,1999)==statpers
tsset id fpdatem
tsfill
drop fpedats fpi

replace statpers=L.statpers if missing(statpers)
replace ticker=ticker[_n-1] if missing(ticker) & id==id[_n-1]

by id: ipolate medest fpdatem, gen(imedest)
drop medest

gen fpi=(fpdatem-mofd(statpers))/12
keep if fpi==int(fpi) & fpi>0 & fpi<=5
drop id

reshape wide imedest fpdatem, i(ticker statpers) j(fpi)
forvalues x=1(1)5 {
	rename imedest`x' medest`x'
}
merge 1:1 ticker statpers using "`LTG'"
drop _merge
order ticker statpers LTG
save $Work/tmp.dta, replace
* ****************************************************************
* merge with ibes split factors
import sas using $WRDS/IBES/adjsum.sas7bdat, case(lower) clear
keep ticker statpers adjspf 
merge 1:1 ticker statpers using $Work/tmp.dta
drop _merge

*keep only firms for which there is at least one earnings forecast
bysort ticker: egen obs=count(medest1)
keep if obs>0
drop obs

* tsset to fill in missing values of adjspf
egen id=group(ticker)
gen datem=mofd(statpers)
format datem %tm

tsset id datem
tsfill
replace adjspf=L.adjspf if adjspf==.
replace adjspf=. 	if adjspf==0

drop if statpers==.

drop id 
save $Work/tmp.dta, replace
* ****************************************************************
* merge with most recent earnings on ibes
import sas $WRDS/IBES/actpsumu_epsus.sas7bdat, clear case(lower)
rename fy0a 	FY0A 
rename fy0edats FY0EDATS
keep ticker statpers measure FY0A shout FY0EDATS cur* 
keep if index(measure,"EPS") & index(curcode,"USD") 

duplicates tag ticker statpers,gen(dup)
tabulate dup
drop dup measure curcode

gen datem=mofd(statpers)
format datem %tm

merge 1:1 ticker datem using $Work/tmp.dta
drop if _merge==1
drop _merge

forvalues x=1(1)5 {
	replace  medest`x'=medest`x'/adjspf
}
gen eps0=FY0A/adjspf
drop datem
* ****************************************************************
* add permcos -- use links from original JPE submission when available (to keep original sample fixed)
merge m:1 ticker statpers using $Raw/ibes01_brown_2019, keepusing(comnam cname permco permno ticker statpers)
drop if _merge==2
drop _merge
* update links for new obs
merge m:1 ticker statpers using $Raw/link_ibes_crsp2022, keepusing(comnam cname permco permno ticker statpers) update
drop if _merge==2 | permco==.
drop if statpers>mdy(1,1,2023)
drop _merge
duplicates tag permco permno statpers, gen(dup)
tabulate dup
drop if dup>0 & shout==.
drop dup
duplicates tag permco permno statpers, gen(dup)
tabulate dup
drop if dup>0 & index(ticker,"1")
drop dup
duplicates tag permco permno statpers, gen(dup)
drop if dup>0 & LTG==. & statpers>mdy(11,1,1981)
drop dup
duplicates drop permco permno statpers, force
* ****************************************
* get mkt cap data 
merge 1:1 permco permno statpers using $Work/mcap
drop if _merge==2
drop _merge

gen datem=mofd(statpers)
format datem %tm
save $Work/tmp, replace
 /* ********************************************************************* */
* Berkshire Hathaway is a unique situation; it has two versions of the same stock: one cheap and one expensive; LTG is missing for one or the other
* Google has similar situation to Berkshire Hathaway
keep if LTG<. & (index(ticker,"GOOG") | index(ticker, "BKHT"))

tsset permno datem
tsfill
replace permco=L.permco if permco==.
replace ticker=ticker[_n-1] if missing(ticker) & permno==permno[_n-1]

bys permco datem: egen mean=mean(LTG)
replace LTG=mean if LTG==. 
drop mean
drop if statpers==. & LTG==.
/* ********************************************************************* */
merge 1:1 permco permno datem using $Work/tmp
drop _merge

save $Work/XSibes.dta, replace
/* ********************************************************************* */
!rm $Work/mcap -f
!rm $Work/tmp  -f
